﻿using System;
using System.Collections.Generic;
using System.Data.Common;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.DataProtection;
using Microsoft.AspNetCore.Hosting;
using Microsoft.AspNetCore.Identity;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;
using QRCodePass.Application.Departments;
using QRCodePass.Application.Students;
using QRCodePass.Application.Students.Dtos;
using QRCodePass.Core.Models;
using QRCodePass.Core.Repositories;
using QRCodePass.EntityFrameworkCore;
using QRCodePass.Mvc.Security.CustomTokenProvider;
using QRCodePass.Mvc.ViewModels.Home;

namespace QRCodePass.Mvc.Controllers
{
    public class HomeController : Controller
    {
        private readonly IRepository<Student, int> _studentRepository;
        private readonly AppDbContext _dbcontext;
        private readonly SignInManager<ApplicationUser> _signInManager;
        private readonly UserManager<ApplicationUser> _userManager;
        private readonly IRepository<Department,int> _departmentRepository;
        private readonly IWebHostEnvironment _webHostEnvironment;
        private readonly ILogger _logger;
        private readonly IStudentService _studentService;

        //IDataProtector提供了Protect() 和 Unprotect() 方法,可以对数据进行加密或者解密。
        private readonly IDataProtector _protector;

        //CreateProtector()方法是IDataProtectionProvider接口提供的,它实例化的名称dataProtectionProvider的方法CreateProtector()需要数据保护字符串
        //所以需要注入我们声明的数据保护用途的链接字符串
        //目前我们只需要保密Student中的id信息。
        public HomeController(IWebHostEnvironment webHostEnvironment,
            ILogger<HomeController> logger,
            IDataProtectionProvider dataProtectionProvider,
            DataProtectionPurposeStrings dataProtectionPurposeStrings,
            IRepository<Student, int> studentRepository,
            IStudentService studentService,
            IRepository<Department, int> departmentRepository,
            SignInManager<ApplicationUser> signInManager,
            UserManager<ApplicationUser> userManager,
            AppDbContext dbcontext)
        {
            _webHostEnvironment = webHostEnvironment;
            _logger = logger;
            _studentRepository = studentRepository;
            _protector = dataProtectionProvider.CreateProtector(
                 dataProtectionPurposeStrings.StudentIdRouteValue);
            _studentService = studentService;
            _dbcontext = dbcontext;
            _signInManager = signInManager;
            _userManager = userManager;
            _departmentRepository = departmentRepository;
        }

        public async Task<IActionResult> Index(GetStudentInput input)
        {
            //获取分页结果
            //var dtos = await _studentService.GetPaginatedResult(input);
            //dtos.Data = dtos.Data.Select(s =>
            //{
            //    //加密ID值并存储在EncryptedId属性中
            //    s.EncryptedId = _protector.Protect(s.Id.ToString());
            //    return s;
            //}).ToList();
            //return View(dtos);
            ApplicationUser uerModel = null;
            if (_signInManager.IsSignedIn(User))
            {
                 uerModel = await _userManager.FindByNameAsync(User.Identity.Name);
            }
            return View(uerModel);
        }

        public async Task<ActionResult> About()
        {
            List<EnrollmentDateGroupDto> groups = new List<EnrollmentDateGroupDto>();
            //获取数据库的上下文链接
            var conn = _dbcontext.Database.GetDbConnection();
            try
            {    //打开数据库链接
                await conn.OpenAsync();
                //建立链接，因为非委托资源所以需要使用using进行内存资源的释放
                using (var command = conn.CreateCommand())
                {
                    string query = @" SELECT A.Id,A.Name,A.DepartmentId,B.FinishCount,C.NeedFinishCount FROM 
                    QRCodePass.Student A
                    LEFT JOIN
                        (select StudentId, COUNT(StudentId) FinishCount  from QRCodePass.ProcessRecord group by StudentId) AS B
                    ON A.Id = B.StudentId
                    LEFT JOIN
                        (select COUNT(1) NeedFinishCount,DepartmentId from QRCodePass.DepartProc GROUP BY DepartmentId) AS C
                    ON A.DepartmentId = C.DepartmentId";


                    command.CommandText = query; //赋值需要执行的sql命令
                    DbDataReader reader = await command.ExecuteReaderAsync();//执行命令
                    if (reader.HasRows)//判断是否有返回行
                    {       //读取行数据 ，将返回值填充到视图模型中
                        while (await reader.ReadAsync())
                        {
                            var row = new EnrollmentDateGroupDto
                            {
                                EnrollmentDate = reader.GetDateTime(0),
                                StudentCount = reader.GetInt32(1)
                            };
                            groups.Add(row);
                        }
                    }
                    //释放使用的所有的资源
                    reader.Dispose();
                }
            }
            finally
            {  //关闭数据库连接。
                conn.Close();
            }
            return View(groups);
        }

        #region 私有方法

        /// <summary>
        /// 解密学生信息
        /// </summary>
        /// <param name="id"> </param>
        /// <returns> </returns>
        private Student DecryptedStudent(string id)
        {
            //使用 Unprotect()方法来解析学生id
            string decryptedId = _protector.Unprotect(id);
            int decryptedStudentId = Convert.ToInt32(decryptedId);
            Student student = _studentRepository.FirstOrDefault(s => s.Id == decryptedStudentId);
            return student;
        }

        /// <summary>
        /// 将照片保存到指定的路径中，并返回唯一的文件名
        /// </summary>
        /// <returns> </returns>
        private string ProcessUploadedFile(StudentCreateViewModel model)
        {
            string uniqueFileName = null;

            if (model.Photos != null && model.Photos.Count > 0)
            {
                foreach (var photo in model.Photos)
                {
                    //必须将图像上传到wwwroot中的images/avatars文件夹
                    //而要获取wwwroot文件夹的路径，我们需要注入 ASP.NET Core提供的webHostEnvironment服务
                    //通过webHostEnvironment服务去获取wwwroot文件夹的路径
                    string uploadsFolder = Path.Combine(_webHostEnvironment.WebRootPath, "images", "avatars");
                    //为了确保文件名是唯一的，我们在文件名后附加一个新的GUID值和一个下划线
                    uniqueFileName = Guid.NewGuid().ToString() + "_" + photo.FileName;
                    string filePath = Path.Combine(uploadsFolder, uniqueFileName);

                    //因为使用了非托管资源，所以需要手动进行释放
                    using (var fileStream = new FileStream(filePath, FileMode.Create))
                    {
                        //使用IFormFile接口提供的CopyTo()方法将文件复制到wwwroot/images/avatars 文件夹
                        photo.CopyTo(fileStream);
                    }
                }
            }
            return uniqueFileName;
        }

        #endregion 私有方法

        public async Task<ActionResult> Statistics()
        {
            var departmentList =await _departmentRepository.GetAllListAsync();
            var statisticsList = new List<StudentStatistics>();
            //获取数据库的上下文链接
            var conn = _dbcontext.Database.GetDbConnection();
            try
            {    //打开数据库链接
                await conn.OpenAsync();
                //建立链接，因为非委托资源所以需要使用using进行内存资源的释放
                using (var command = conn.CreateCommand())
                {
                    string query = @" SELECT A.Id,A.Name,A.DepartmentId,ISNULL(B.FinishCount,0) FinishCount,C.NeedFinishCount FROM 
                    QRCodePass.Student A
                    LEFT JOIN
                        (select StudentId, COUNT(StudentId) FinishCount  from QRCodePass.ProcessRecord group by StudentId) AS B
                    ON A.Id = B.StudentId
                    LEFT JOIN
                        (select COUNT(1) NeedFinishCount,DepartmentId from QRCodePass.DepartProc GROUP BY DepartmentId) AS C
                    ON A.DepartmentId = C.DepartmentId";


                    command.CommandText = query; //赋值需要执行的sql命令
                    DbDataReader reader = await command.ExecuteReaderAsync();//执行命令
                    if (reader.HasRows)//判断是否有返回行
                    {       //读取行数据 ，将返回值填充到视图模型中
                        while (await reader.ReadAsync())
                        {
                            var row = new StudentStatistics
                            {
                                StudentId = reader.GetInt32(0),
                                StudentName = reader.GetString(1),
                                DepartmentId = reader.GetInt32(2),
                                FinishCount = reader.GetInt32(3),
                                NeedFinishCount = reader.GetInt32(4),
                            };
                            statisticsList.Add(row);
                        }
                    }
                    //释放使用的所有的资源
                    reader.Dispose();
                }

                foreach (var statist in statisticsList)
                {
                        statist.DepartmentName = departmentList.SingleOrDefault(x => x.Id.Equals(statist.DepartmentId))?.Name;
                }
            }
            finally
            {  //关闭数据库连接。
                conn.Close();
            }

            return View(statisticsList);
        }
    }


}